package com.neuedu.it.newidea.tools;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.neuedu.it.newidea.dao.CommonMapper;
import org.springframework.ai.tool.annotation.Tool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class SqlExecutionService {
    @Autowired
    private CommonMapper commonMapper;

    @Autowired
    private ObjectMapper objectMapper;

    /**
     * 执行更新类型的SQL语句（增强版，专门处理批量更新场景）
     */
    @Tool(
            name = "executeUpdateSql",
            description = "执行增删改SQL语句，支持批量操作。" +
            "参数要求：" +
            "1. sql：完整的SQL语句（必须使用命名参数，如#{name}，不能为空）；" +
            "2. params：SQL参数（必须是JSON对象格式，如{\"name\":\"张三\",\"id\":1001}，无参数时传空对象{}）"
    )
    public String executeUpdateSql(String sql, Map<String, Object> params) {
        // 1. 验证SQL语句非空
        if (sql == null || sql.trim().isEmpty()) {
            return "执行失败：SQL语句不能为空，请检查生成的SQL是否正确";
        }

        // 2. 处理参数默认值
        Map<String, Object> safeParams = params != null ? params : Map.of();

        try {
            // 3. 针对批量更新特殊处理
            String upperSql = sql.trim().toUpperCase();
            if (upperSql.startsWith("UPDATE") && upperSql.contains("IN (SELECT")) {
                // 检查是否包含子查询，提示用户可能需要的执行时间
                SqlParam sqlParam = new SqlParam(sql, safeParams);
                int affectedRows = commonMapper.executeUpdate(sqlParam);
                return String.format("批量更新成功，影响行数：%d。注意：包含子查询的批量更新已执行", affectedRows);
            }

            // 4. 普通更新操作
            SqlParam sqlParam = new SqlParam(sql, safeParams);
            int affectedRows = commonMapper.executeUpdate(sqlParam);
            return "操作成功，影响行数：" + affectedRows;
        } catch (Exception e) {
            // 5. 详细异常信息，便于排查问题
            String errorMsg = e.getMessage();
            if (errorMsg.contains("You can't specify target table")) {
                return "执行失败：MySQL不支持在更新语句的子查询中直接引用目标表。" +
                        "建议改为：UPDATE users u JOIN (SELECT DISTINCT s_userid FROM creations) c " +
                        "ON u.u_id = c.s_userid SET u.u_score = u.u_score + 10";
            }
            return "操作失败：" + errorMsg;
        }
    }

    /**
     * 执行查询类型的SQL语句
     */
    @Tool(
            name = "executeQuerySql",
            description = "执行查询SQL语句，返回所有结果。参数：sql=完整的SQL语句，params=SQL参数（无参数时传空Map）"
    )
    public String executeQuerySql(String sql, Map<String, Object> params) {
        // 同样添加SQL非空校验
        if (sql == null || sql.trim().isEmpty()) {
            return "查询失败：SQL语句不能为空";
        }

        try {
            SqlParam sqlParam = new SqlParam(sql, params != null ? params : Map.of());
            List<Map<String, Object>> rawResult = commonMapper.executeQuery(sqlParam);

            List<Map<String, Object>> cleanedResult = rawResult.stream()
                    .map(this::cleanMapNullValue)
                    .collect(Collectors.toList());

            return objectMapper.writeValueAsString(cleanedResult);
        } catch (JsonProcessingException e) {
            return "查询成功，但结果序列化失败：" + e.getMessage();
        } catch (Exception e) {
            return "查询失败：" + e.getMessage();
        }
    }

    private Map<String, Object> cleanMapNullValue(Map<String, Object> map) {
        if (map == null) return Map.of();

        return map.entrySet().stream()
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        entry -> entry.getValue() != null ? entry.getValue() : "null"
                ));
    }

    public static class SqlParam {
        private String sql;
        private Map<String, Object> params;

        public SqlParam(String sql, Map<String, Object> params) {
            this.sql = sql;
            this.params = params;
        }

        public String getSql() {
            return sql;
        }

        public Map<String, Object> getParams() {
            return params;
        }
    }
}


